def get_sql_lst(id_lst):
    main = f'''
    CREATE TEMPORARY table main as (
            select distinct pjuuid,estdate,industryphy,industryco,reccaprmb,regcap,pripid
            from cs_mid_baseinfo_valid
            where estdate < date_sub(curdate(),interval 1 year)  and pjuuid in ({id_lst})
    
    ) ;'''

    pjjd_list2 = f'''
    CREATE TEMPORARY table pjjd_list2  as 
    (
    select 	
    distinct 
        pjuuid	,
        AZ1	,
        AZ2	,
        AZ3	,
        AZ4	,
        AZ5	,
        AZ6	,
        AZ7	,
        AZ8	,
        AZ9	,
        AZ10	,
        AZ11	,
        AZ12	,
        AZ13	,
        A0	,
        A1	,
        A2	,
        A3	,
        A4	,
        A5	,
        A6	,
        A7	,
        A8	,
        A9	,
        A10	,
        A11	,
        A12	,
        A13	,
        A14	,
        A16	,
        A18	,
        A20	,
        A21	,
        A22	,
        A23	,
        A24	,
        A25	,
        A26	,
        A27	,
        A28	,
        A29	,
        A30	,
        A31	,
        A32	,
        A33	,
        AZ14	
    from 	pjjd_list	
    where pjuuid is not null and pjuuid <> ''
    and pjuuid in ({id_lst})
    ); '''

    target = '''
    CREATE TEMPORARY table target as (
    
    
    SELECT
      t1.pjuuid,
        AZ2 as credit_id,
    case 			
    when cast(substr(AZ14,1,2) as signed)  in 	(1, 2, 3, 4, 5)	then 	"A"
    when cast(substr(AZ14,1,2) as signed)  in 	(6, 9, 12, 25, 30, 11)	then 	"B06091230C25"
    when cast(substr(AZ14,1,2) as signed)  in 	(13, 14, 15, 27, 41, 42, 16)	then 	"C13_15274142"
    when cast(substr(AZ14,1,2) as signed)  in 	(17, 18)	then 	"C1718"
    when cast(substr(AZ14,1,2) as signed)  in 	(19)	then 	"C19"
    when cast(substr(AZ14,1,2) as signed)  in 	(20, 21, 22, 23, 24)	then 	"C20_24"
    when cast(substr(AZ14,1,2) as signed)  in 	(26, 28)	then 	"C2628"
    when cast(substr(AZ14,1,2) as signed)  in 	(29)	then 	"C29"
    when cast(substr(AZ14,1,2) as signed)  in 	(31, 32, 33, 38, 39, 40, 37)	then 	"C31_3338_40"
    when cast(substr(AZ14,1,2) as signed)  in 	(34, 35)	then 	"C3435"
    when cast(substr(AZ14,1,2) as signed)  in 	(36)	then 	"C36"
    when cast(substr(AZ14,1,2) as signed)  in 	(43, 61, 62, 64, 65, 69, 70, 71, 72, 74, 75, 81, 82, 90)	then 	"C43_R90"
    when cast(substr(AZ14,1,2) as signed)  in 	(44, 45, 46)	then 	"D44_46"
    when cast(substr(AZ14,1,2) as signed)  in 	(49, 50, 47, 48)	then 	"E4950"
    when cast(substr(AZ14,1,2) as signed)  in 	(51, 52)	then 	"F"
    when cast(substr(AZ14,1,2) as signed)  in 	(53, 54, 55, 59, 60)	then 	"G53_5559_60"
    when cast(substr(AZ14,1,2) as signed)  in 	(58, 63, 73, 76, 77, 78, 79, 80, 83, 84, 85, 86, 87, 88, 89, 92)	then 	"G58_S92"
    else 'bup' end as model_type ,
        CASE
        WHEN A25 LIKE '%%是%%' THEN
        1 ELSE 0 
        END AS A25,-- 环境管理制度 A25
    CASE
    
        WHEN A6 LIKE '%%是%%' THEN
        1 ELSE 0 
        END AS A6, -- 是否生产绿色产品 A6
    case when  A7  like '是' or A8 like '是' then 1 else 0 end as cust , -- 下游应用方向 cust 
    case when A9 like '是' or A10  like '是' or A11 like '是' then 1 else 0 end  as supp ,  -- 节能环保原材料使用
    case when  A12 like '是' or A13  like '是' or A14 like '是' then 1 else 0 end  as equip, -- 低能耗生产设备使用
    case when  A16 != 'x3' then 1 else 0 end as feishui , -- 废水排放管理
    case when  A18 != 'x3' then 1 else 0 end as feiqi ,-- 废气排放管理
    case when  A20 != 'x3' then 1 else 0 end as feizha , -- 废渣排放管理
    CASE
        WHEN A21 = '否' THEN 1
        WHEN A21 != '否' AND A24 = '是' THEN 0.5
        WHEN A21 != '否' AND (A22 = '是' OR A23 = '是') THEN 1
        ELSE 0  end as weifei ,-- 危险废弃物排放管理 
    case when A26 = '是' then 1 else 0 end  as A26, -- A26是否采用电子化运营管理
    case when  A27 = '是' or  A28 = '是' then 1 else 0 end  as jianzhu, -- jianzhu 绿色建筑及建筑节能
    case when A29 = '是' or A30 = '是' then 1 else 0 end as shebei, -- shebei设备节能
    (
            CASE
                WHEN A0 IS NOT NULL AND A0 <> '' AND A0 <> '非绿' THEN A2
                ELSE 0
            END +
            CASE
                WHEN A3 IS NOT NULL AND A3 <> '' AND A3 <> '非绿' THEN A5
                ELSE 0
            END
        ) / 100.0 AS lyzb,  -- 绿营占比
      A31 / (GREATEST(
            COALESCE(t1.A33, 0),
            COALESCE(t2.xssr, 0),
            COALESCE(t3.vendinc, 0)
        ) * (100 - coalesce(AZ11,0)) / 100 ) AS ynqd, -- 用能强度
            A32 / (GREATEST(
            COALESCE(t1.A33, 0),
            COALESCE(t2.xssr, 0),
            COALESCE(t3.vendinc, 0)
        ) * (100 - coalesce(AZ11,0)) / 100 ) as ysqd   -- 用水强度
    
    from pjjd_list2 t1 
    
    left join
    
    (select pjuuid, substr(sbny,1,4) as sbny ,sum(xssr) as xssr  
    from 
    zx_swg_nsxx_xssr_valid
    where substr(sbny,1,4) >  year(date_sub(curdate(),interval 10 year))-- year(date_sub(now(),interval 10 year))
    group by pjuuid,substr(sbny,1,4) 
    )
    
    t2
    
    on  t1.AZ13 = t2.sbny and t1.pjuuid = t2.pjuuid
    
    left join 
    
    (select pjuuid,substr(year,1,4) as year ,sum(VENDINC) as VENDINC
    from cs_yr_asset_valid 
    where substr(year,1,4) >  year(date_sub(curdate(),interval 10 year)) -- year(DATE_SUB(now(),interval 10 year))
    group by pjuuid,substr(year,1,4) 
    ) t3 
    on t1.AZ13 = t3.year and t1.pjuuid = t3.pjuuid
    where t1.pjuuid is not null and t1.pjuuid != ''
    
    
    );'''

    qycxrd_cnt_12m = '''
    CREATE TEMPORARY table qycxrd_cnt_12m AS (
    -- 企业诚信认定
    select t1.pjuuid,t2.qycxrd_cnt_12m  
    from 
    main t1  left join 
    (
    SELECT DISTINCT
        pjuuid,
        rdsj as dt ,
        1 as qycxrd_cnt_12m
    FROM
        zx_gsj_xyglsfqy_valid 
    WHERE rdsj = year(date_sub(curdate(),interval 1 year)) 
    
    UNION ALL
    
    SELECT DISTINCT
        pjuuid,
        nd dt ,
        1 as qycxrd_cnt_12m
    FROM
        zx_gsj_szmdxx_valid
    WHERE nd = year(date_sub(curdate(),interval 1 year)) 
    
    ) t2 on  t1.pjuuid = t2.pjuuid
    );'''

    clns = '''
    
    CREATE TEMPORARY table clns AS (
    select pjuuid ,
                    floor(DATEDIFF(curdate(),estdate)/ 365 ) AS clns
    from 
        main 
    );
    '''

    zybgcs_12m_cnt = '''
    -- 企业主营范围变更次数
    CREATE TEMPORARY table zybgcs_12m_cnt as (
    select t1.pjuuid ,t2.zybgcs_12m_cnt
    from 
    main t1 
    left join 
    (
    SELECT DISTINCT pjuuid,1 as zybgcs_12m_cnt 
    from cs_mid_altitem_valid
    where altitem = 12
    and  datediff(now(),altdate) < 365  and datediff(now(),altdate) > 0
    ) t2  
    
    on t1.pjuuid = t2.pjuuid 
    );'''

    sum_yjse = ''' 
    CREATE TEMPORARY table sum_yjse as 
    (
    SELECT
        t1.pjuuid,
        sum( t2.yjse ) AS sum_yjse ,
        count(t2.yjse) as ttm_nsmonths
    FROM
        main t1
        LEFT JOIN (
    SELECT
        pjuuid,
        yjse 
    FROM
        zx_swg_nsxx_sk_valid 
    WHERE
        datediff( now(), str_to_date( concat( rkny, '-01' ), '%%Y-%%m-%%d' ) ) < 365 AND datediff( now(), str_to_date( concat( rkny, '-01' ), '%%Y-%%m-%%d' ) ) > 0 
        ) t2 ON t1.pjuuid = t2.pjuuid 
    GROUP BY
        t1.pjuuid
    );
     '''

    pwxk = '''
    -- pwxk  排污许可
    CREATE TEMPORARY table pwxk as (
    select t1.pjuuid ,t2.pwxk
    from main t1 left join 
    (
    SELECT DISTINCT pjuuid,1 as pwxk 
    from zx_hb_paiwu_valid
    where now() > yxqq  and  date_sub(curdate(),interval 1 year) < yxqz
    
    )
    t2 
    on t1.pjuuid = t2.pjuuid
    ) ;'''

    count_hpsp = '''
    CREATE TEMPORARY table count_hpsp as (
    select t1.pjuuid,t2.count_hpsp 
    from main t1  left join 
    
    (SELECT distinct pjuuid, 1 as count_hpsp 
    FROM zx_hb_xmhz_valid
    where spsj between  date_sub(curdate(),interval 1 year)  and  curdate()
    ) t2 on t1. pjuuid =t2.pjuuid 
    ) ;
    '''

    ldbzcf_12m_cnt = '''
    CREATE TEMPORARY table ldbzcf_12m_cnt as 
    (
    select t1.pjuuid,t2.ldbzcf_12m_cnt
    from main t1 left join 
    (
    SELECT pjuuid,1 as ldbzcf_12m_cnt  FROM
    (
    SELECT pjuuid,
                case when larq is null then sdcfwsrq else larq end as cfrq
    from zx_rs_chufa_valid
    ) t
    where  cfrq between  date_sub(curdate(),interval 1 year)   and curdate()
    ) t2 
    on t1.pjuuid = t2.pjuuid 
    
    );'''

    bldk_12m_cnt = '''
    CREATE TEMPORARY table bldk_12m_cnt as 
    (
    select 
    t1.pjuuid ,t2.bldk_12m_cnt
    from main t1 left join 
    (
    SELECT distinct pjuuid
                ,1 as bldk_12m_cnt
    from zx_rmyh_bldk_valid
    where  dqrq between  date_sub(curdate(),interval 1 year)   and curdate()
    ) t2 on  t1.pjuuid = t2.pjuuid 
    );'''

    ysqfxx_12m_cnt = '''
    -- 用水欠费信息 ysqfxx_12m_cnt
    CREATE TEMPORARY table ysqfxx_12m_cnt as 
    (
    select t1.pjuuid , t2.ysqfxx_12m_cnt
    from 
    main t1 left join 
    (
    select 
    pjuuid ,count(je) as  ysqfxx_12m_cnt
    from 
    (
    select   
           pjuuid,
                                 CASE 
                        WHEN CAST(SUBSTR(REPLACE(tjny, '-', ''), 5, 2) AS UNSIGNED) BETWEEN 1 AND 12 THEN
                            DATE(CONCAT(SUBSTR(REPLACE(tjny, '-', ''), 1, 4), '-', SUBSTR(REPLACE(tjny, '-', ''), 5, 2), '-01'))
                        ELSE
                            NULL
                    END AS dt,
                 je	 
    FROM legal_entity_water_arrearage_valid 
    )	 t 
    where dt  between  date_sub(curdate(),interval 1 year)  and curdate()  and je != 0 and je is  not null 
    group by pjuuid 
    ) t2  on t1.pjuuid = t2.pjuuid 
    );'''

    qyyzsxxw_cnt_12m = '''
    -- 企业严重失信行为
    CREATE TEMPORARY table qyyzsxxw_cnt_12m as 
    (
    SELECT  t1.pjuuid ,t2.qyyzsxxw_cnt_12m
    from 
    main t1 left join 
    (
    SELECT DISTINCT pjuuid
    ,1 as qyyzsxxw_cnt_12m
    from legal_entity_break_faith_valid
    where break_faith_level = '严重失信行为'
    and STR_TO_DATE(break_faith_date,'%%Y-%%m-%%d')  between  date_sub(curdate(),interval 1 year)  and curdate() 
    
    ) t2 on t1.pjuuid = t2.pjuuid
    
    ) ;'''

    sfxz_cnt_12m = '''
    
    -- 司法协助 sfxz_cnt_12m
    CREATE TEMPORARY table sfxz_cnt_12m as 
    (
    select  t1.pjuuid , t2.sfxz_cnt_12m
    from 
    main t1 
    left join 
    (
    select * ,1 as sfxz_cnt_12m
    from (
    SELECT 
        pjuuid, 
        executedate AS dt, 
        exerulnum
    FROM 
        cs_pub_justiceinfo_valid
    WHERE 
        executeitem = 4
    union all 
    SELECT 
        pjuuid, 
        frofrom AS dt, 
        exerulnum
    FROM 
        cs_pub_justiceinfo_valid
    WHERE 
        executeitem = 1
    ) t
    where str_to_date(dt,'%%Y-%%m-%%d') between  date_sub(curdate(),interval 1 year)  and curdate() 
    
    ) t2 
    on 
        t1.pjuuid = t2.pjuuid 
    ) ;
    '''

    frbg_12m_cnt = '''
    CREATE TEMPORARY table frbg_12m_cnt as 
    (
    SELECT
    t1.pjuuid,t2.frbg_12m_cnt
    FROM main t1 
    left join 
    (
    SELECT pjuuid ,count(altdate) as frbg_12m_cnt
    FROM
    (
    SELECT DISTINCT pjuuid, altitem, altdate
    from cs_mid_altitem_valid
    where altitem = 3 and str_to_date(altdate,'%%Y-%%m-%%d')  between  date_sub(curdate(),interval 1 year)  and curdate() 
    
    ) t
    GROUP BY pjuuid ) t2
    on 
        t1.pjuuid = t2.pjuuid
        );'''

    gqzrgqbg_12m_cnt = '''
    -- 股权转让、股权变更  gqzrgqbg_12m_cnt
    CREATE TEMPORARY table gqzrgqbg_12m_cnt as 
    (
    SELECT t1.pjuuid ,t2.gqzrgqbg_12m_cnt
    from 
    main t1 
    left join 
    (
    select pjuuid,1 as gqzrgqbg_12m_cnt
    FROM cs_mid_altitem_valid
    where altitem = '51'
    and STR_TO_DATE(altdate,'%%Y-%%m-%%d') between  date_sub(curdate(),interval 1 year)  and curdate() 
    ) t2 
    on 
        t1.pjuuid = t2.pjuuid
        );'''

    yclx_12m_max = '''
    -- yclx_12m_max 工商信息异常
     CREATE TEMPORARY table yclx_12m_max as (
    select t1.pjuuid ,t2.yclx_12m_max
    from 
    main t1 
    left join 
    (
    SELECT DISTINCT pjuuid
                            ,case when specausecn like '%%年度报告%%' and specausecn not like '%%弄虚作假%%' then 1
                                  when specausecn like '%%第十条%%' then 2
                                  when specausecn like '%%联系%%' then 3
                                  when specausecn like '%%弄虚作假%%' then 4
                                  else 5 end as yclx_12m_max
                      from cs_pub_opadetail_valid
        where 
            str_to_date(substr(abntime,1,10),'%%Y-%%m-%%d')   between  date_sub(curdate(),interval 1 year)  and curdate() 
            ) t2 on t1.pjuuid = t2.pjuuid 
            );'''

    gscf_new_cnt_12m = '''
    CREATE TEMPORARY table gscf_new_cnt_12m as 
    (
    SELECT 
         t1.pjuuid ,t2.gscf_new_cnt_12m
    FROM
    main t1 
    left join 
    (
    SELECT DISTINCT pjuuid,1 as  gscf_new_cnt_12m from zx_ga_cfxx_valid
    where 
        larq  between  date_sub(curdate(),interval 1 year)  and curdate() 
    ) t2  on t1.pjuuid = t2.pjuuid 
    );'''

    otherpun_cnt_12m = '''CREATE TEMPORARY table otherpun_cnt_12m as 
    (
    SELECT 
         t1.pjuuid ,t2.otherpun_cnt_12m
    FROM
    main t1 
    left join 
    (
    SELECT DISTINCT pjuuid, 1 as otherpun_cnt_12m
                      from cs_pub_otherpunish_valid
    
    where 
        pendecissdate  between  date_sub(curdate(),interval 1 year)  and curdate() 
    ) t2  on t1.pjuuid = t2.pjuuid 
    );'''

    qylach_12m_cnt = '''
    CREATE TEMPORARY table qylach_12m_cnt as (
    SELECT 
         t1.pjuuid ,t2.qylach_12m_cnt
    FROM
    main t1 
    left join 
    (
    SELECT DISTINCT pjuuid
                                            , 1 as qylach_12m_cnt
                      from zx_fy_qylaxx_valid
                      where ssdw in ('被告','被上诉人','被执行人','被申请执行人','被告人')
    and
        larq  between  date_sub(curdate(),interval 1 year)  and curdate() 
    ) t2  on t1.pjuuid = t2.pjuuid 
        );'''

    count_hbcf = '''
    CREATE TEMPORARY table count_hbcf as 
    (
    SELECT 
    t1.pjuuid ,t2.count_hbcf
    FROM 
    main t1 
    left join 
    (
    SELECT DISTINCT pjuuid,1 as count_hbcf
    from zx_hb_cfxx_valid
    where larq between  date_sub(curdate(),interval 1 year)  and curdate() 
    ) t2 on t1.pjuuid = t2.pjuuid 
    );'''

    sxss = '''
    CREATE TEMPORARY table sxss as 
    ( 
    select  t1.pjuuid ,t2.sxss
    from 
    main t1
    left join 
    (
    SELECT pjuuid, 
    case when sxss like '%%逃匿%%' then 4
    when sxss like '%%转移%%' then 3
    when sxss like '%%拒不%%' then 2
    else 1 end as sxss
    from zx_rs_qxqy_valid
    where ccrq between date_sub(curdate(),interval 1 year)  and curdate() 
    ) t2 on t1.pjuuid = t2.pjuuid 
    );'''

    gacf_12m_cnt = '''
    CREATE TEMPORARY table gacf_12m_cnt as 
    (
    SELECT t1.pjuuid,t2.gacf_12m_cnt
    FROM
    main t1
    left join 
    (
    SELECT DISTINCT pjuuid, 1 as gacf_12m_cnt from zx_ga_cfxx_valid
    where larq between date_sub(curdate(),interval 1 year)  and curdate() 
    ) t2 on t1.pjuuid =t2.pjuuid
    );'''

    sql_concat = '''
    select 
                t.*,
                t0.pripid,
                t1.qycxrd_cnt_12m,
                t2.clns,
                t3.zybgcs_12m_cnt,
                t4.sum_yjse,
                t4.ttm_nsmonths,
                t5.pwxk,
                t6.count_hpsp,
                t7.count_hbcf,
                t8.ldbzcf_12m_cnt,
                t9.bldk_12m_cnt,
                t10.sxss,
                t11.ysqfxx_12m_cnt,
                t12.qyyzsxxw_cnt_12m,
                t13.sfxz_cnt_12m,
                t14.frbg_12m_cnt,
                t15.gqzrgqbg_12m_cnt,
                t16.yclx_12m_max,
                t17.gacf_12m_cnt,
                t18.gscf_new_cnt_12m,
                t19.otherpun_cnt_12m,
                t20.qylach_12m_cnt
    
    
    
    from 
        target t
    left join 
        main t0
    on 
        t.pjuuid = t0.pjuuid
    left join 
        qycxrd_cnt_12m t1 
    on 
        t0.pjuuid = t1.pjuuid
    left join 
        clns t2
    on  
        t1.pjuuid = t2.pjuuid 
    left join
        zybgcs_12m_cnt t3 
    on 
        t1.pjuuid = t3.pjuuid 
    left join 
        sum_yjse t4 
    on 
        t1.pjuuid = t4.pjuuid 
    left join 
        pwxk  t5 
    on  
        t1.pjuuid = t5.pjuuid 
    left join 
        count_hpsp t6 
    on
        t1.pjuuid  = t6.pjuuid 
    left join 
        count_hbcf t7
    on 
        t1.pjuuid = t7.pjuuid
    
    left join 
        ldbzcf_12m_cnt t8
    on 
        t1.pjuuid = t8.pjuuid 
    left join
        bldk_12m_cnt t9 
    on 
        t1.pjuuid = t9.pjuuid 
    
    left join 
      sxss t10 
    on 
        t1.pjuuid = t10.pjuuid
    
    
    left join 
        ysqfxx_12m_cnt t11
    on 
        t1.pjuuid = t11.pjuuid 
    
    left join 
        qyyzsxxw_cnt_12m t12
    on
        t1.pjuuid = t12.pjuuid
    
    left join 
        sfxz_cnt_12m t13
    on 
        t1.pjuuid = t13.pjuuid
    
    left join 
        frbg_12m_cnt t14
    
    on 
        t1.pjuuid = t14.pjuuid
    
    left join 
        gqzrgqbg_12m_cnt t15
    on 
        t1.pjuuid = t15.pjuuid 
    
    left join 
        yclx_12m_max t16 
    on 
        t1.pjuuid = t16.pjuuid 
    
    left join 
        gacf_12m_cnt t17
    on 
        t1.pjuuid = t17.pjuuid
    
    left join 
        gscf_new_cnt_12m t18
    on 
        t1.pjuuid = t18.pjuuid
    
    left join 
        otherpun_cnt_12m t19
    on 
        t1.pjuuid = t19.pjuuid
    
    left join 
        qylach_12m_cnt t20
    on 
        t1.pjuuid = t20.pjuuid;'''

    sql_lst = [main, pjjd_list2, target, qycxrd_cnt_12m, clns, zybgcs_12m_cnt, sum_yjse, pwxk, count_hpsp, ldbzcf_12m_cnt,
               bldk_12m_cnt, ysqfxx_12m_cnt, qyyzsxxw_cnt_12m, sfxz_cnt_12m, frbg_12m_cnt, gqzrgqbg_12m_cnt, yclx_12m_max,
               gscf_new_cnt_12m, otherpun_cnt_12m, qylach_12m_cnt, count_hbcf, sxss, gacf_12m_cnt]
    return sql_lst,sql_concat